package jsyf.oa.dao.device;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.dao.system.LogsDao;
import jsyf.oa.entity.device.DeviceEntity;
import jsyf.oa.entity.device.DeviceSampleMainEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by LT on 2018/4/14.
 */
@Repository
public class DeviceSampleDao extends BaseDao {
    @Autowired
    LogsDao logsDao;

    public DeviceSampleDao() {
        super.iBaseEntity = new DeviceSampleMainEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, DeviceSampleMainEntity.class);
    }

    public List<JSONObject> getDispaceId(String displaceId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_con_replacement.MAIN_ID AS conId,\n" +
                "t_con_replacement.ID AS replacementId,\n" +
                "t_con_replacement.REPLACEMENTS_NAME AS replacementName,\n" +
                "t_dvc_displace.ID AS displaceId\n" +
                "FROM\n" +
                " t_con_replacement \n" +
                "LEFT JOIN t_dvc_displace ON t_dvc_displace.REPLACEMENT_ID=t_con_replacement.ID \n" +
                "WHERE t_con_replacement.MAIN_ID='" + displaceId + "'");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getBrand(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT \n" +
                "v_sys_user.companyId AS companyId ,\n" +
                "v_sys_user.companyName AS companyName ,\n" +
                "t_dvc_brand.BRAND_NAME AS brandName ,\n" +
                "t_dvc_brand.ID  AS brandId\n" +
                "FROM \n" +
                "t_dvc_brand \n" +
                "LEFT JOIN t_sys_company_2_brand ON t_dvc_brand.ID=t_sys_company_2_brand.BRAND_ID\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.companyId=t_sys_company_2_brand.COMPANY_ID \n" +
                "WHERE 1=1");
        sql.append(condition);
        sql.append(" ORDER BY t_dvc_brand.BRAND_NAME");
        return super.queryForJsonList(sql.toString(), params);
    }


    public List<JSONObject> getModel(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_dvc_model.ID AS id,\n" +
                "\tt_dvc_model.MODEL_NAME AS modelName\n" +
                "FROM\n" +
                "\tt_dvc_model\n" +
                "LEFT JOIN t_dvc_brand ON t_dvc_brand.ID = t_dvc_model.BRAND_ID\n" +
                "WHERE \n" +
                "1=1");
        sql.append(condition);
        return super.queryForJsonList(sql.toString(), params);
    }

    public String getStorageTime(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT CASE WHEN(IFNULL(to_days(NOW()) - to_days(LEFT(STOCK_TIME, 10)),0))>0 ");
        sql.append(" THEN IFNULL(to_days(NOW()) - to_days(LEFT(STOCK_TIME, 10)),0)");
        sql.append(" ELSE 0 END FROM ");
        sql.append(" t_dvc_sample WHERE id = '" + id + "'");
        return super.queryForString(sql.toString());
    }

    public List<JSONObject> getDeviceSampleList(String condition, Object[] params, int requestPage, int pageSize, String key) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * from( SELECT\n" +
                "\tt_dvc_sample.ID AS id,\n" +
                "\tPICK_UP_DATE AS pickUpDate,\n" +
                "\tSHUTTLE_CONTACTS AS shuttle_contacts,\n" +
                "\tSHUTTLE_TEL AS shuttle_tel,\n" +
                "\tt_dvc_sample.`FK_COMPANY_ID` AS fkcompanyId," +
                " t_dvc_sample.FREE_MATURITY_DATE as freeMaturityDate,\n" +
                "\tt_sys_company.`NAME` AS companyName,\n" +
                "\tt_dvc_sample.BRAND_NAME AS brandName,\n" +
                "\tt_dvc_sample.DEVICE_TYPE_NAME AS deviceTypeName,\n" +
                "\tt_dvc_sample.MODEL_NAME AS modelName," +
                " t_dvc_sample.TON_CONFIG AS tonConfig,\n" +
                "\tSEQUENCE AS sequence,\n" +
                "\tBUCKET_TYPE AS bucket_type," +
                "BUCKET_TYPE AS bucketType," +
                "t_dvc_sample.AUDIT_FLAG AS auditFlag," +
                "t_dvc_sample.AUDIT_NAME AS auditName," +
                "t_dvc_sample.AUDIT_ID as auditId,\n" +
                "\tSTORE_ADDRESS AS storeAddress,\n" +
                "\tSTATE AS state," +
                "t_sys_company.SORT_NO as companySort,");
        if ("已交".equals(key)) {
            sql.append(" t_con_main.BUYER as buyer,");
        }
        sql.append(
                "t_dvc_model.SORT_NO as modelSort," +
                        "CASE WHEN STATE='已交'\n" +
                        "  THEN 1\n" +
                        "WHEN STATE = '已退货' THEN 2\n" +
                        "ELSE 0\n" +
                        "END AS paixu,\n" +
                        "\tSETTLE AS settle,t_dvc_sample.REMARK as remark,t_dvc_sample.CREATE_USER_ID as createUserId,\n" +
                        "\tt_dvc_sample.CREATE_TIME AS createTime,t_dvc_sample.PROVINCE_NAME as provinceName,\n" +
                        "t_dvc_sample.CITY_NAME as cityName,t_dvc_sample.COUNTY_NAME as countyName,\n" +
                        "IFNULL(t_dvc_outbound.CREATE_TIME,'0') AS stateTime\n" +
                        "FROM\n" +
                        "\tt_dvc_sample\n" +
                        " LEFT JOIN t_sys_company ON t_dvc_sample.FK_COMPANY_ID = t_sys_company.ID " +
                        " LEFT JOIN t_dvc_model ON t_dvc_sample.FK_MODEL_ID=t_dvc_model.ID \n" +
                        "LEFT JOIN t_dvc_outbound ON t_dvc_outbound.SAMPLE_ID=t_dvc_sample.ID\n"
        );
        if ("已交".equals(key)) {
            sql.append( "  LEFT JOIN t_con_main ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n");
        }
        sql.append(" WHERE 1=1  ");
        sql.append(condition);
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);
        return jsonList;
    }

    public List<JSONObject> getcertificateList(String sampleId) {

        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + sampleId + "'" + "AND attach_module=6", new Object[]{});
    }

    public List<JSONObject> getdevicePictures(String sampleId) {

        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + sampleId + "'" + "AND attach_module=7", new Object[]{});
    }

    public List<JSONObject> getdrayPictures(String sampleId) {

        return super.queryForJsonList("SELECT * FROM v_com_attach WHERE 1=1 AND moduleId=" + " '" + sampleId + "'" + "AND attach_module=8", new Object[]{});
    }

    public JSONObject getAllCount(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tCOUNT(1) AS allNum,\n" +
                "\tCOUNT(STATE = '提车在途' OR NULL) AS getNow,\n" +
                "\tCOUNT(STATE in('库存','寄存') OR NULL) AS no,\n" +
                "\tCOUNT(STATE = '交付在途' OR NULL) AS outNow,\n" +
                "\tCOUNT(STATE = '已交' OR NULL) AS outNum,\n" +
                "\tCOUNT(STATE = '退货在途' OR NULL) AS exitNum,\n" +
                "\tCOUNT(STATE = '移库在途' OR NULL) AS moveNow,\n" +
                "\tCOUNT(STATE = '已退货' OR NULL) AS exitNow\n" +
                "FROM\n" +
                "\tt_dvc_sample LEFT JOIN t_sys_company ON t_dvc_sample.FK_COMPANY_ID = t_sys_company.ID " +
                " LEFT JOIN t_dvc_model ON t_dvc_sample.FK_MODEL_ID=t_dvc_model.ID WHERE 1=1  \n");
        sql.append(condition);
        JSONObject countList = super.queryForJsonObject(sql.toString());
        return countList;

    }

    public List<JSONObject> getNewSample(String condition, String type) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT");
        sql.append(" t_dvc_sample.ID AS id,\n" +
                "  t_sys_company.`NAME` as companyName,\n" +
                "  t_dvc_sample.BRAND_NAME as brandName,\n" +
                "  STORE_ADDRESS as storeAddress,\n" +
                "  STATE as state," +
                "SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) as subStoreAddress ,\n");
        if ("库存地".equals(type)) {
            sql.append(" \n" +
                    "  CASE WHEN  STORE_ADDRESS=\"直发客户交机\" THEN 9999\n" +
                    "  WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"哈尔滨\" THEN 0\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"长春\" THEN 1\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"沈阳\" THEN 2\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"徐州\" THEN 3\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"常州\" THEN 4\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"合肥\" THEN 5\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"石家庄\" THEN 6\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1) = \"兰州\" THEN 7\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1)= \"银川\" THEN 8\n" +
                    "WHEN SUBSTRING(SUBSTRING_INDEX(STORE_ADDRESS,\"市\",1),LOCATE(\"省\",STORE_ADDRESS)+1)= \"长沙\" THEN 9" +
                    " ELSE \n" +
                    "CASE WHEN COMPANY_NAME=\"黑龙江灜源\" THEN 0\n" +
                    "     WHEN COMPANY_NAME=\"吉林清源\" THEN 1\n" +
                    "     WHEN COMPANY_NAME=\"辽宁灜湖\" THEN 2\n" +
                    "     WHEN COMPANY_NAME=\"江苏滚滚向前\" THEN 4\n" +
                    "WHEN COMPANY_NAME=\"安徽灜湖\" THEN 5\n" +
                    "WHEN COMPANY_NAME=\"河北灜源\" THEN 6\n" +
                    "WHEN COMPANY_NAME=\"甘肃&宁夏\" THEN 8\n" +
                    "WHEN COMPANY_NAME=\"湖南清源\" THEN 9\n" +
                    "     END\n" +
                    "END AS storeAddressNo,");
        }
        sql.append(" t_dvc_sample.MODEL_NAME as modelName," +
                "t_dvc_model.TON_CONFIG as tonConfig,\n" +
                "  BUCKET_TYPE as bucketType,\n" +
                "  SEQUENCE as sequence,\n" +
                "  t_dvc_sample.REMARK as remark,\n" +
                "  PICK_UP_DATE as pickUpDate,\tt_dvc_sample.PROVINCE_NAME as provinceName,\n" +
                "t_dvc_sample.CITY_NAME as cityName,left(t_dvc_sample.UPDATE_TIME,10) as updateTime,\n" +
                "t_dvc_sample.COUNTY_NAME as countyName");
        sql.append(" FROM");
        sql.append(" t_dvc_sample  LEFT JOIN t_sys_company ON t_dvc_sample.FK_COMPANY_ID=t_sys_company.ID");
        sql.append(" LEFT JOIN t_dvc_brand on t_dvc_sample.FK_BRAND_ID=t_dvc_brand.ID");
        sql.append(" LEFT JOIN t_dvc_model ON t_dvc_sample.FK_MODEL_ID=t_dvc_model.ID");
        sql.append(" WHERE STATE NOT in(\"已交\",\"已退货\") AND AUDIT_FLAG=1");
        if (!StringUtils.isBlank(condition)) {
            sql.append(condition);
        }
        if ("提车公司".equals(type)) {
            sql.append(" ORDER BY t_sys_company.SORT_NO,t_dvc_brand.SORT_NO,t_dvc_model.TON_CONFIG,t_dvc_sample.STORE_ADDRESS desc,t_dvc_sample.PICK_UP_DATE DESC");
        } else if ("品牌".equals(type)) {
            sql.append(" ORDER BY t_dvc_sample.BRAND_NAME,t_sys_company.SORT_NO,t_dvc_model.TON_CONFIG,t_dvc_sample.STORE_ADDRESS desc");
        } else {
            sql.append(" ORDER BY storeAddressNo,t_sys_company.SORT_NO,t_dvc_sample.STORE_ADDRESS,t_dvc_brand.SORT_NO,t_dvc_model.TON_CONFIG,t_dvc_sample.PICK_UP_DATE DESC");
        }
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getDownByCondition(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT * from(SELECT\n" +
                "\tt_dvc_sample.COMPANY_NAME as companyName,\n" +
                "  t_dvc_sample.BRAND_NAME as brandName,\n" +
                "  t_dvc_sample.DEVICE_TYPE_NAME as deviceTypeName,\n" +
                "  t_dvc_sample.MODEL_NAME as modelName,\n" +
                "\tt_dvc_sample.SEQUENCE as sequence,\n" +
                "  t_dvc_sample.TON_CONFIG as tonConfig,\n" +
                "  t_dvc_sample.BUCKET_TYPE as bucketType,\n" +
                "  t_dvc_sample.PRODUCTION_DATE as productionDate,\n" +
                "  CASE WHEN t_dvc_sample.NAMEPLATE_ATTACH_NAME IS NULL\n" +
                "  THEN \"无\" ELSE t_dvc_sample.NAMEPLATE_ATTACH_NAME\n" +
                "  END as namePlateAttachName,\n" +
                "  t_dvc_sample.STOCK_TIME as stockTime,\n" +
                "  CASE WHEN(IFNULL(to_days(NOW()) - to_days(LEFT(t_dvc_sample.STOCK_TIME, 10)),0))>0 \n" +
                "  THEN IFNULL(to_days(NOW()) - to_days(LEFT(t_dvc_sample.STOCK_TIME, 10)),0)\n" +
                "  ELSE 0 END as storeDuration,\n" +
                "  t_dvc_sample.STATE as state,\n" +
                "  t_dvc_sample.STORE_ADDRESS as storeAddress,\n" +
                "  t_dvc_sample.ACTURAL_DELIVERY_DATE as actualDeliveryDate,\n" +
                "  t_dvc_sample.financing as financing,\n" +
                "  t_dvc_sample.PICK_UP_DATE as pickUpDate,\n" +
                "  t_dvc_sample.PICK_UP_PRICE as pickupPrice,\n" +
                "  (select GROUP_CONCAT(t_dvc_parts_records.PARTS_NAME) from t_dvc_parts_records WHERE t_dvc_parts_records.SAMPLE_ID=t_dvc_sample.ID) as partsName,\n" +
                "  CASE WHEN t_dvc_sample.INVOICE_ATTACH_NAME IS NULL\n" +
                "  THEN \"无\" ELSE t_dvc_sample.INVOICE_ATTACH_NAME\n" +
                "  END as invoiceAttachName,\n" +
                "  CASE WHEN t_dvc_sample.CERTIFICATION_ATTACH_NAME IS NULL\n" +
                "  THEN \"无\" ELSE t_dvc_sample.CERTIFICATION_ATTACH_NAME\n" +
                "  END as certificationAttachName,\n" +
                "  t_dvc_sample.PAYMENT as payment,\n" +
                "  t_dvc_sample.FREE_MATURITY_DATE as freeMaturityDate,\n" +
                "  t_dvc_repayment_plan.PAYMENT_DATE as paymentDate,\n" +
                "  t_dvc_repayment_plan.PAYEE as payee,\n" +
                "  t_dvc_repayment_plan.PAYMENT_MONEY as paymentMoney,\n" +
                "  t_dvc_sample.SETTLE as settle,\n" +
                "  t_dvc_sample.TRAFFIC_STARTING as trafficStarting,\n" +
                "\tt_dvc_sample.TRAFFIC_DESTINATION as trafficDestination,\n" +
                "  t_dvc_sample.TRANSPORT_COMPANY as transportCompany,\n" +
                "\tt_dvc_sample.TRANSPORT_START_TIME as transportStartTime,\n" +
                "  t_dvc_sample.TRANSPORT_END_TIME as transportEndTime,\n" +
                "  CASE WHEN  t_dvc_sample.SPECIAL_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_sample.SPECIAL_FEE END\n" +
                "  as specialFee,\n" +
                "  CASE WHEN  t_dvc_sample.DEDUCTION_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_sample.DEDUCTION_FEE END\n" +
                "  as deductionFee,\n" +
                "  t_dvc_sample.DEDUCTION_REMARK as deductionRemark,\n" +
                "  t_dvc_sample.DRAY_LICENSE as drayLicense,\n" +
                "  t_dvc_sample.DRIVER_NAME as driverName,\n" +
                "  t_dvc_sample.DRIVER_TEL as driverTel,\n" +
                "  CASE WHEN t_dvc_outbound.TYPE =\"0\"\n" +
                "  THEN \"出库\"\n" +
                "  WHEN t_dvc_outbound.TYPE =\"1\"\n" +
                "   THEN \"移库\"\n" +
                "  WHEN t_dvc_outbound.TYPE =\"2\"\n" +
                "   THEN \"退货\" \n" +
                "  ELSE \"无\" END AS outType,\n" +
                "  \n" +
                "  t_con_main.CONTRACT_NO as contractNo,\n" +
                "  t_dvc_outbound.PICK_UP_ADDRESS as pickUpAddress,\n" +
                "  t_dvc_outbound.DELIVERY_ADDRESS as delivery_address,\n" +
                "  t_dvc_outbound.TRANSPORT_COMPANY as outTransportCompany,\n" +
                "\tt_dvc_outbound.TRANSPORT_START_TIME as outTransportStartTime,\n" +
                "  t_dvc_outbound.TRANSPORT_END_TIME as outTransportEndTime,\n" +
                "  CASE WHEN  t_dvc_outbound.SPECIAL_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_outbound.SPECIAL_FEE END\n" +
                "  as outSpecialFee,\n" +
                "  CASE WHEN  t_dvc_outbound.DEDUCTION_FEE=\"0.00\"\n" +
                "  THEN \"无\"\n" +
                "  ELSE t_dvc_outbound.DEDUCTION_FEE END\n" +
                "  as outDeductionFee,\n" +
                "  t_dvc_outbound.DEDUCTION_REMARK as outDeductionRemark,\n" +
                "  t_dvc_outbound.DRAY_LICENSE as outDrayLicense,\n" +
                "  t_dvc_outbound.DRIVER_NAME as outDriverName,\n" +
                "  t_dvc_outbound.DRIVER_TEL as outDriverTel,\n" +
                "  t_dvc_sample.REMARK as remark,\n" +
                "  CASE WHEN STATE='已交'\n" +
                "  THEN 1 ELSE 0\n" +
                "  END as paixu\n" +
                "FROM\n" +
                "\tt_dvc_sample\n" +
                "LEFT JOIN t_dvc_repayment_plan ON t_dvc_sample.ID = t_dvc_repayment_plan.SAMPLE_ID\n" +
                "LEFT JOIN t_dvc_outbound ON t_dvc_sample.ID=t_dvc_outbound.SAMPLE_ID\n" +
                "LEFT JOIN t_con_main ON t_con_main.ID=t_dvc_outbound.CONTRACT_ID\n" +
                "LEFT JOIN t_dvc_model ON t_dvc_sample.FK_MODEL_ID=t_dvc_model.ID\n" +
                "WHERE 1=1");
        sql.append(condition);
        sql.append(" GROUP BY t_dvc_sample.ID");
        sql.append(" ) as sample ORDER BY sample.paixu ASC,sample.storeAddress DESC,sample.brandName,sample.tonConfig,sample.pickUpDate DESC");
        return super.queryForJsonList(sql.toString(), params);
    }

    @Override
    public Integer update(IBaseEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.saveLogs(entity);
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }

    @Override
    public Integer add(IBaseEntity entity) {
        entity.setId(super.generateKey());
        entity.setCreateTime(super.getCurrentTime());
        entity.setUpdateTime(super.getCurrentTime());
        logsDao.addLogs(entity);
        return super.insert(entity.getTableName(), entity.getJSONObject());
    }

    public JSONObject getDeliveryAddressBySampleId(String sampleId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "DELIVERY_ADDRESS AS deliveryAddress\n" +
                "FROM\n" +
                "t_dvc_outbound\n" +
                "WHERE 1=1\n" +
                "AND TYPE=0\n" +
                "AND SAMPLE_ID='" + sampleId + "'\n" +
                "LIMIT 0,1");
        JSONObject deliveryAddress = super.queryForJsonObject(sql.toString());
        return deliveryAddress;
    }
}
